How to use CockroachDB with your Django application on Ubuntu

Django is a high-level flexible framework for building Python applications quickly. Applications run on Django store data, by default, into a SQLite database file, but lots of Django users find themselves needing to switch to a more performant database in production, one with better availability or scalability.

This is a short tutorial on using Django with CockroachDB as your database. At the time of writing, django-cockroachdb library is available in two versions, (2 and 3). This post focuses on version 2 specifically. This tutorial is based on the Digital Ocean tutorial "How to Use PostgreSQL with your Django Application on Ubuntu 14.04." There's one important difference between this and the PostgreSQL tutorial (which I'll highlight again, below): this tutorial uses Python3. For everything else, we'll follow the Digital Ocean PostgreSQL/Django tutorial as is.

Since we’re going to need role-based access management (RBAC) features of CockroachDB, we'll require an enterprise license. Feel free to request a trial or try this tutorial in a cockroach demo environment. Using the cockroach demo command will enable enterprise features for 60 minutes---more than enough time to complete this tutorial.

Install the Components from the Ubuntu Repositories

sudo apt-get update
sudo apt-get install python3-pip python3-dev libpq-dev

Create a Database and Database User

My CockroachDB instance is running on 10.142.0.46 in insecure mode on port 26257, but if you're playing along at home, feel free to run it locally. These details will be necessary when we’re going to configure Django. You can validate connectivity by accessing CRDB SQL shell with the following:

./cockroach sql --insecure --host=10.142.0.46 --port=26257

First things first, enable enterprise features by passing your trial license and organization to the following properties:

SET CLUSTER SETTING cluster.organization = 'Acme Company';

SET CLUSTER SETTING enterprise.license = 'xxxxxxxxxxxx';

CREATE DATABASE myproject;

Set the desired database as default.

SET DATABASE = myproject;

Since we’re using CRDB in insecure mode, WITH PASSWORD command will be ignored.

CREATE USER myprojectuser;

Create a role for our Django app.

CREATE ROLE django;

Verify that the role has been created.

> SHOW roles;
  role_name
+-----------+
  admin
  django
(2 rows)

Grant privileges to the Django role you created:

GRANT CREATE, INSERT, UPDATE, SELECT ON DATABASE myproject TO django;

Next, verify the grants exist on the database.

> SHOW GRANTS ON DATABASE myproject;

  database_name |    schema_name     | grantee | privilege_type
+---------------+--------------------+---------+----------------+
  myproject     | crdb_internal      | admin   | ALL
  myproject     | crdb_internal      | django  | CREATE
  myproject     | crdb_internal      | django  | SELECT
  myproject     | crdb_internal      | root    | ALL
  myproject     | information_schema | admin   | ALL
  myproject     | information_schema | django  | CREATE
  myproject     | information_schema | django  | SELECT
  myproject     | information_schema | root    | ALL
  myproject     | pg_catalog         | admin   | ALL
  myproject     | pg_catalog         | django  | CREATE
  myproject     | pg_catalog         | django  | SELECT
  myproject     | pg_catalog         | root    | ALL
  myproject     | public             | admin   | ALL
  myproject     | public             | django  | CREATE
  myproject     | public             | django  | SELECT
  myproject     | public             | root    | ALL

(16 rows)

Add myprojectuser to Django role:

GRANT django TO myprojectuser;

Now, you can verify access to CockroachDB using the newly created user.

./cockroach sql --insecure --host=10.142.0.46 --port=26257 --database=myproject --user=myprojectuser

Install Django within a Virtual Environment

As a reminder, I’m using Python3, so the only difference between this and the Digital Ocean Django tutorial is that we're using pip3, not pip.

pip3 install virtualenv

Now, we’re ready to install Django and CockroachDB library.

pip3 install django psycopg2 django-cockroachdb

Configure the Django Database Settings

Here, we'll set CockroachDB as the database engine.

DATABASES = {
    'default': {
        'ENGINE': 'django_cockroachdb',
        'NAME': 'myproject',
        'USER': 'myprojectuser',
        'PASSWORD': '',
        'HOST': '10.142.0.46',
        'PORT': '26257',
    }
}

Migrate the Database and Test your Project

python3 manage.py makemigrations
python3 manage.py migrate
python3 manage.py createsuperuser
python3 manage.py runserver 0.0.0.0:8000'

This article was originally posted on Artem Ervits' personal blog.